import re
import mysql.connector
import datetime
import time


def extract_procedure_name(sql_code):
    match = re.search(r"create procedure (\w+)\(", sql_code)
    if match:
        return match.group(1)
    else:
        return "Procedure name not found"


def get_current_timestamp_as_string_with_milliseconds():
    # 获取当前时间戳，单位为秒
    timestamp = time.time()

    # 将时间戳转换为datetime对象
    dt_object = datetime.datetime.fromtimestamp(timestamp)

    # 获取毫秒部分
    milliseconds = int((timestamp - int(timestamp)) * 1000)

    # 将datetime对象转换为字符串，并添加毫秒部分
    dt_string = dt_object.strftime('%Y%m%d_%H%M%S') + f'_{milliseconds:03d}'

    return dt_string


def remove_comments_and_blank_lines(original_file_path):
    # 构建新文件的路径
    new_file_path = original_file_path.replace('.sql', '_mdf_'+get_current_timestamp_as_string_with_milliseconds()+'.sql')
    # 打开原始文件和目标文件
    with open(original_file_path, 'r', encoding='utf-8') as file_in, open(new_file_path, 'w',
                                                                          encoding='utf-8') as file_out:
        # 逐行读取原始文件
        for line in file_in:
            # 去除行首和行尾的空白字符
            stripped_line = line.strip()
            # 检查行是否非空且不以'--'开头
            if stripped_line and not stripped_line.startswith('--'):
                # 如果行非空且不以'--'开头，则转为小写后写入目标文件，保留原有的换行符
                file_out.write(line.lower())
                # 返回新文件的路径
    return new_file_path


procedure_sql = remove_comments_and_blank_lines("d:/aa.sql")
# 替换后的文件
print(procedure_sql)
# 读取文件内容
with open(procedure_sql, 'r', encoding='utf-8') as file:
    content = file.read()


# 替换函数，用于生成替换后的变量名
def replace_v_vars(match):
    var_name = match.group(0)
    if var_name not in var_mapping:
        replace_v_vars.count += 1
        var_mapping[var_name] = f'a{replace_v_vars.count}'
    return var_mapping[var_name]


# 用于存储每个v_变量名对应的替换名称
var_mapping = {}

# 初始化替换次数计数器
replace_v_vars.count = 0

# 使用正则表达式替换所有以v_开头的变量
replaced_content = re.sub(r'\bv_\w+\b', replace_v_vars, content).lower()

# 输出替换后的内容
print(replaced_content)
# 如果需要，可以将替换后的内容写回文件
# with open('aa.txt', 'w', encoding='utf-8') as file:  # 确保使用正确的编码写入文件
#     file.write(replaced_content)
with open('replaced_content.sql', 'w', encoding='utf-8') as new_file:
    new_file.write(replaced_content)

# 连接到MySQL数据库
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="123",
    database="qlysn_sproot"
)

# 创建一个cursor对象来执行命令
cursor = conn.cursor()

drop_sql = "DROP PROCEDURE IF EXISTS "+extract_procedure_name(replaced_content) + ";"
cursor.execute(drop_sql)
# 执行SQL命令来创建存储过程
cursor.execute(replaced_content)

# 提交事务
conn.commit()

# 关闭cursor和连接
cursor.close()
conn.close()

print("存储过程已成功创建。")

#
# CREATE PROCEDURE GetSelectStatement (
# 	IN tableName VARCHAR (256),
# 	OUT selectStatement TEXT
# )
# BEGIN
#
# SET @SQL = NULL;
#
# SELECT
# 	GROUP_CONCAT(
# 		CONCAT(
# 			'`',
# 			COLUMN_NAME,
# 			'` AS v_',
# 			COLUMN_NAME
# 		)
# 		ORDER BY
# 			ORDINAL_POSITION SEPARATOR ', '
# 	) INTO @COLUMNS
# FROM
# 	INFORMATION_SCHEMA. COLUMNS
# WHERE
# 	TABLE_SCHEMA = DATABASE ()
# AND TABLE_NAME = tableName;
#
#
# SET selectStatement = CONCAT(
# 	'SELECT ',
# 	@COLUMNS,
# 	' FROM ',
# 	tableName,
# 	' u  where 1=1 '
# );
#
#
# END
#
# CALL GetSelectStatement (
# 	'sys_user' ,@selectStatement
# );
#
# SELECT
# 	@selectStatement
#
# SELECT
# 		`id` AS v_id,
# 		`age` AS v_age,
# 		`create_date` AS v_create_date,
# 		`email` AS v_email,
# 		`last_update_time` AS v_last_update_time,
# 		`login_name` AS v_login_name,
# 		`password` AS v_password,
# 		`salt` AS v_salt,
# 		`user_status` AS v_user_status,
# 		`user_name` AS v_user_name,
# 		`post_id` AS v_post_id,
# 		`phone` AS v_phone
# 	FROM
# 		sys_user u

